#!/usr/bin/python3.8

import sqlite3
import pymysql
import time


class Sqlite():
    ''' python3 sqlite3 数据库操作类 '''
    def __init__(self, sqlite_file):
        self.sqlite_file = sqlite_file
        self.conn = sqlite3.connect(self.sqlite_file)
        self.cursor = self.conn.cursor()

    def select(self, sql):
        self.cursor.execute(sql)
        data = self.cursor.fetchall()
        return data

    def execute(self, sql):
        self.cursor.execute(sql)

    def commit(self):
        self.conn.commit()

    def close(self):
        self.conn.close()


class MySQL():
    ''' python3 mysql 数据库操作类 '''
    def __init__(self, host, port, user, passwd, db):
        self.host = host
        self.port = port
        self.user = user
        self.passwd = passwd
        self.db = db
        self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db)
        self.cursor = self.conn.cursor()

    def select(self, sql):
        self.cursor.execute(sql)
        data = self.cursor.fetchall()
        return data

    def execute(self, sql):
        pass
        #self.cursor.execute(sql)

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()

    def close(self):
        self.conn.close()


class MemcacheResource():
    ''' Memcache CMDB数据同步至Sqlite类 '''

    # 获取CMDB所有Memcache资产
    def getCmdbResource(self):
        #mysql_obj = MySQL(mysql_info['host'], mysql_info['port'], mysql_info['user'], mysql_info['passwd'], mysql_info['db'])
        sql = 'select meta_id,meta_name,meta_memo,addr_open_addr from meta_rds where status=1 and addr_open_addr LIKE "%memcache%";'
        data = mysql_obj.select(sql)
        memcache_id_list_mysql = []
        memcache_list_mysql = []
        for i in data:
            memcache_id_list_mysql.append(i[0])
            memcache_list_mysql.append(i)
        return memcache_id_list_mysql, memcache_list_mysql

    # 获取Sqlite所有Memcache资产
    def getSqliteResource(self):
        #sqlite_obj = Sqlite(sqlite_file)
        sql = 'select * from memcache'
        data = sqlite_obj.select(sql)
        memcache_id_list_sqlite = []
        memcache_list_sqlite = []
        for i in data:
            memcache_id_list_sqlite.append(i[0])
        return memcache_id_list_sqlite

    # 同步memcache数据信息
    def syncMemcacheResource(self):

        # 获取列表
        memcache_id_list_mysql, memcache_list_mysql = self.getCmdbResource()
        memcache_id_list_sqlite = self.getSqliteResource()

        # 新增/冗余/更新的MemcacheID列表
        insert_memcache_id_list = set(memcache_id_list_mysql) - set(memcache_id_list_sqlite)
        delete_memcache_id_list = set(memcache_id_list_sqlite) - set(memcache_id_list_mysql)
        update_memcache_id_list = set(memcache_id_list_mysql) & set(memcache_id_list_sqlite)

        # 删除冗余memcache资源
        for del_memcache_id in delete_memcache_id_list:
            sql = 'DELETE FROM memcache WHERE id="{}"'.format(del_memcache_id)
            sqlite_obj.execute(sql)
            sqlite_obj.commit()

        # 新增memcache资源
        for i in memcache_list_mysql:
            if i[0] in insert_memcache_id_list:
                print('插入数据ID: {}'.format(i[0]))
                sql = 'INSERT INTO memcache (id, name, account) VALUES ("{}", "{}", "{}")'.format(i[0], i[1], 'memcache')
                sqlite_obj.execute(sql)
                sqlite_obj.commit()
            elif i[0] in update_memcache_id_list:
                print('更新数据ID: {}'.format(i[0]))
                sql = 'UPDATE memcache SET name="{}", account="{}" WHERE id="{}"'.format(i[1], i[2], i[0])
                sqlite_obj.execute(sql)
                sqlite_obj.commit()


# 数据库信息
sqlite_file = '/data/cms.db'
sqlite_obj = Sqlite(sqlite_file)
mysql_info = {'host': 'host', 'port': 3306, 'user': 'user', 'passwd': 'passwd', 'db': 'db'}
mysql_obj = MySQL(mysql_info['host'], mysql_info['port'], mysql_info['user'], mysql_info['passwd'], mysql_info['db'])

# 同步数据
if __name__ == '__main__':
    print('开始同步: {}'.format(time.strftime('%Y-%m-%d %H:%M:%S')))
    memcache_resourcde_obj = MemcacheResource()
    memcache_resourcde_obj.syncMemcacheResource()
    print('结束同步: {}\n\n\n'.format(time.strftime('%Y-%m-%d %H:%M:%S')))

sqlite_obj.close()
mysql_obj.close()